# -*- coding:utf-8 -*-
from models.CoCode import CoCode

print 'Hello', 'SQLAlchemy', '!'


from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import setting
from utils.LinqAlchemy import ModelMixin, DataSet


DB_CONNECT_STRING = 'mysql://airflow:password@172.17.0.2:3306/airflowdb?charset=utf8'
#engine = create_engine(DB_CONNECT_STRING, echo=True)
engine = create_engine(DB_CONNECT_STRING, echo=setting.echo)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()


#session.execute('create database abc')
print session.execute('show databases').fetchall()

print session.execute('select * from Co_Code').first()
print session.execute('select * from Co_Code where CodeId=:id', {'id': 2}).first()





from sqlalchemy import func, or_, not_


cocode = CoCode(CName = '天气', CText='晴', CValue = 'qing', CSort=1, Status = 1)
session.add(cocode)

#session.commit()   #没有调用commit()的话并不会插入数据库



query = session.query(CoCode)
print query
print query.statement

for code in query:
    print code.CText

print query.all()   # 返回的是一个类似列表的对象
print query.first().CName   # 记录不存在时，first() 会返回 None
#print query.one().CName # 不存在，或有多行记录时会抛出异常
print query.filter(CoCode.CName == '天气').first().CText
print query.get(3).CName    # 以主键获取
#print query.filter(u"CName = '客户意向'").first().CText    # 会产生运行时警告

from sqlalchemy import text

print query.filter(text(u"CName = '客户意向'")).first().CText   # 消除警告的方法


query2 = session.query(CoCode.CText)
print query2.all()  # 每行是个元组
print query2.limit(2).all() # Take
print query2.offset(2*1).all()    # Skip
print query2.order_by(CoCode.CText).all()
print query2.order_by('CText').all()
print query2.order_by(CoCode.CText.desc()).all()
print query2.order_by(text('CText desc')).all()
print session.query(CoCode.CText).order_by(CoCode.CName, CoCode.CSort).all()

print str(session.query(CoCode.CText).order_by(CoCode.CName.desc(), CoCode.CSort).all()).decode('string_escape')   #无效

import uniout
print session.query(CoCode.CText).order_by(CoCode.CName.desc(), CoCode.CSort).all()    #显示汉字


#print query2.filter(CoCode.CName == u'天气').scalar() # 如果有记录，返回第一个元素, 否则返回None;有多行记录时会抛出异常








# 扩展基类方法1.定义一个新类，将它的方法设置为基类的方法

#from utils.LinqAlchemy import ModelMixin
#
# BaseModel.Hello = ModelMixin.Hello
# print CoCode.Hello()
#
# session.CoCode = CoCode
# print session.CoCode.Hello()


session.CoCode = CoCode
print session.CoCode.Hello()


session.CoCode = DataSet(session, CoCode)
print session.CoCode.Where(CoCode.CName == "天气").Count()
#print session.CoCode.ToList().Count()
print len(session.CoCode.ToList())

print session.CoCode.OrderBy(CoCode.CName, CoCode.CSort).ToList()
#print session.CoCode.Select(CoCode.CText).OrderBy(CoCode.CName, CoCode.CSort).ToList()


from utils.LinqAlchemy import linq_words

session.Select = session.query
print linq_words(session.Select(CoCode.CText)).OrderBy(CoCode.CName, CoCode.CSort).ToList()